/*
 * UWT 2014
 */
package database;

import static com.google.common.base.Preconditions.*;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import controller.Controls;

/**
 * Establishes connection to MySQL database and handles query requests.
 * 
 * @author Matt Adams
 */
public class CandoDatabase {
	private static final Logger logger = LoggerFactory.getLogger(CandoDatabase.class);
	
	
	private static final String JDBC_DRIVER_CLASS = "com.mysql.jdbc.Driver";
	/**
	 * Database connection.
	 */
    private Connection connect;
    
    /**
     * Database statement used to execute queries and updates.
     */
    private Statement statement;
    
    /**
     * Values contained in protein compound interact table.
     */
    private double[][] data;
    
    /**
     * Compound names of protein compound interact table.
     */
    private Object[] compoundNames;
    
    /**
     * Protein IDs of protein compound interact table.
     */
    private Object[] proteinIDs;
    
    /**
     * Map of compound IDs to corresponding compound names.
     */
    private Map<Integer, String> compoundMap;
    
    /**
     * VisualCando application controls object.
     */
    private Controls controls;
    
    //Database connection requirements.
    private String host;    
    private String port;   
    private String username;    
    private String password;    
    private String schema;
    
    //Cando table attributes
    private int numCompounds;
    private int numVerticalPartitions;
    private String candoTableName;
    private String candoFile;
    
    //Compound mapping table attributes
    private String compoundTableName;
    private String compoundMappingFile;

    /**
     * Constructs CandoDatabase object.
     */
    public CandoDatabase(final Controls controls) {
    	this.controls = controls;
    	loadProperties();
    	try {
    		Class.forName(JDBC_DRIVER_CLASS);
    		this.connect = DriverManager.getConnection(createDatabaseConnectionString());
    		this.statement = this.connect.createStatement();
    		logger.info("Connection to database established successfully.");
    		
    	} catch (ClassNotFoundException e) {
    		logger.error("MySQL jdbc driver class: {}. Not Found. Error: {}", JDBC_DRIVER_CLASS, e);
    		this.controls.displayErrorMessage("Not Found. MySQL jdbc driver class: " + 
    									      JDBC_DRIVER_CLASS + "\n\nError Message: " + e.toString());
    	} catch (SQLException e) {
    		logger.error("Error connecting to database: {}", e);
    		this.controls.displayErrorMessage("Error connecting to database. Attempted to connect to:\n" +
    										  "host: " + this.host +
    										  "\nport: " + this.port +
    										  "\nusername: " + this.username +
    										  "\npassword: " + this.password +
    										  "\nschema: " + this.schema +
    										  "\n\nError Message: " + e.toString());
    	}
    }
    
    /**
     * Executes database script.
     * 
     * @param script Database script.
     */
    public void executeScript(String script) {
		try {
			this.statement.executeUpdate(script);
			logger.info("Successful execution of database script: " + script);
		} catch (SQLException e) {
			logger.error("Failed to execute script: {}. Error message: {}", script, e);
			this.controls.displayErrorMessage("Failed to execute following database script: " + script +
					                          "\n\nError Message: " + e.toString());
		}   	
    }
    
    /**
     * Processes query request of cando table.
     * 
     * @param compoundNames Compound IDs.
     * @param proteinIDs Protein IDs.
     * @throws SQLException Exception thrown from SQL error.
     */
    public void processQuery(final String[] compoundNames, final String[] proteinIDs) throws SQLException {
    	this.compoundMap = queryCompoundNames(compoundNames);
    	int totalProteinIDs = 0;
    	if (proteinIDs == null || proteinIDs.length < 1) {
    		final ResultSet queryTotalProteins = executeQuery("select count(*) from full_protein_interact_1;");
    		queryTotalProteins.next();
    		totalProteinIDs = queryTotalProteins.getInt(1);
    	} else {
    		totalProteinIDs = proteinIDs.length;
    	}
    	this.proteinIDs = new Object[totalProteinIDs];
    	this.data = new double[totalProteinIDs][this.compoundMap.keySet().size()];
    	final String[] partitionQueries = createPartitionQueries(proteinIDs);
    	int dataColPrevMax = 0;
    	for (int i = 0; i < partitionQueries.length; i++) {
    		if (partitionQueries[i] != null) {
    			final ResultSet queryResult = executeQuery(partitionQueries[i]);
    			ResultSetMetaData metaData = queryResult.getMetaData();
    			int colCnt = metaData.getColumnCount();
    			int rowNum = 0;
    			int dataColIndex = dataColPrevMax;
    			int dataColStart = dataColPrevMax;
    			while (queryResult.next()) {
    				for (int j = 2; j <= colCnt; j++) {
    					this.data[rowNum][dataColIndex] = queryResult.getDouble(j);
    					dataColIndex++;
    				}

    				if (i == 0) {
    					this.proteinIDs[rowNum] = queryResult.getString(1);
    				}
    				rowNum++;
    				dataColPrevMax = dataColIndex;
    				dataColIndex = dataColStart;
    			}
    		} 
    	}
    }
    
    /**
     * Returns data from most recent query.
     * 
     * @return Returns data from query.
     */
    public double[][] getData() {
    	return this.data;
    }
    
    /**
     * Returns compounds from most recent query.
     * 
     * @return Returns list of compounds.
     */
    public Object[] getCompounds() {
    	return this.compoundNames;
    }
    
    /**
     * Returns protein IDs from most recent query.
     * 
     * @return Returns list of protein IDs.
     */
    public Object[] getProteins() {
    	return this.proteinIDs;
    }
       
    /**
     * Returns database connection object.
     * 
     * @return Returns database connection object.
     */
    public Connection getDBConnection() {
    	return this.connect;
    }
    
    /**
     * Returns total number of compounds in cando table.
     * @return Returns number of compounds.
     */
    public int getNumCompounds() {
    	return this.numCompounds;
    }
    
    /**
     * Returns number of vertical partitions in cando SQL database.
     * @return Returns number of vertical partitions.
     */
    public int getNumVerticalPartitions() {
    	return this.numVerticalPartitions;
    }
    
    /**
     * @return Returns database schema.
     */
    public String getSchema() {
    	return this.schema;
    }
    
    /**
     * @return Returns cando table name.
     */
    public String getCandoTableName() {
    	return this.candoTableName;
    }
    
    /**
     * @return Returns compound mapping table name.
     */
    public String getCompoundTableName() {
    	return this.compoundTableName;
    }
    
    /**
     * @return Returns compound mapping file.
     */
    public String getCompoundMappingFile() {
    	return this.compoundMappingFile;
    }
    
    /**
     * @return Returns cando file.
     */
    public String getCandoFile() {
    	return this.candoFile;
    }
    
    /**
     * Displays error message.
     */
    public void displayErrorMessage(final String errorMessage) {
    	this.controls.displayErrorMessage(errorMessage);
    }
    
    public boolean checkTableExists(final String tableName) {
    	boolean result = false;
    	try {
    		DatabaseMetaData dbm = this.connect.getMetaData();

    		// check if table is there
    		ResultSet tables = dbm.getTables(null, null, tableName, null);
    		if (tables.next()) {
    			result = true;
    		}
    	} catch (SQLException e) {
    		// TODO Auto-generated catch block
    		e.printStackTrace();
    	}
    	return result;
    }
    
    /**
     * Creates queries for each of the vertical partitions of the cando SQL database.
     * 
     * @param proteinIDs List of protein IDs.
     * @return Returns queries as strings for each vertical partition of the cando database.
     */
    private String[] createPartitionQueries(final String[] proteinIDs) {
    	checkNotNull(this.compoundMap);
    	final List<Integer> sortedCompoundIDs = asSortedList(this.compoundMap.keySet());
    	Collections.sort(sortedCompoundIDs);
    	this.compoundNames = new Object[sortedCompoundIDs.size()];
    	final StringBuilder[] partitionQueries = new StringBuilder[this.numVerticalPartitions];
    	final String[] result = new String[partitionQueries.length];
    	final int numCompoundsPerPartition = this.numCompounds / this.numVerticalPartitions;
		int index = 0;
		int compoundEndIndex;
    	for (int i = 0; i < partitionQueries.length; i++) {
    		partitionQueries[i] = new StringBuilder();
    		partitionQueries[i].append("select `protein ID`"); 
    		compoundEndIndex = numCompoundsPerPartition * (i + 1);
    		if (i == partitionQueries.length - 1) {
    			compoundEndIndex++;
    		}
    		int count = 0;
    		while (index < sortedCompoundIDs.size() && sortedCompoundIDs.get(index) <= compoundEndIndex) {
    			partitionQueries[i].append(", `" + sortedCompoundIDs.get(index) + "`");
    			this.compoundNames[index] = this.compoundMap.get(sortedCompoundIDs.get(index));
    			count++;
    			index++;
    		}
    		if (count != 0) {
    			partitionQueries[i].append(" from `" + this.candoTableName + "_" + (i + 1) + "`");
    			if (proteinIDs == null || proteinIDs.length < 1 || proteinIDs[0].equals("")) {
    				partitionQueries[i].append(";");
    			} else {
    				partitionQueries[i].append(" where ");
    				for (int j = 0; j < proteinIDs.length - 1; j++) {
    					partitionQueries[i].append(" `protein ID` = \"" + proteinIDs[j] + "\" or");
    				}
    				partitionQueries[i].append(" `protein ID` = \"" + proteinIDs[proteinIDs.length - 1] + "\";");
    			}
    			result[i] = partitionQueries[i].toString();
    		} else {
    			result[i] = null;
    		}
    	}
    	return result;   	
    }
    
    /**
     * Queries compound names and returns a map of compound IDs to corresponding compound names.
     * 
     * @param compoundNames List of compound names.
     * @return Returns map of compound IDs to compound names.
     * @throws SQLException Exception thrown from SQL error.
     */
    private Map<Integer, String> queryCompoundNames(final String[] compoundNames) throws SQLException {
    	final Map<Integer, String> compoundMap = new HashMap<Integer, String>();
    	final StringBuilder sb = new StringBuilder();
    	if (compoundNames != null && compoundNames.length > 0) {   	
    		sb.append("select `compound_ID`, `compound_name` from `compound_mapping` where ");
    		for (int i = 0; i < compoundNames.length - 1; i++) {
    			sb.append("`compound_name` = \"" + compoundNames[i] + "\" or ");    		
    		}
    		sb.append("`compound_name` = \"" + compoundNames[compoundNames.length - 1] + "\";");    	
    	} else {
    		//Query returns all compound IDs and compound names.
    		sb.append("select `compound_ID`, `compound_name` from `compound_mapping`;");
    	}
    	ResultSet databaseReturn = executeQuery(sb.toString());

    	while(databaseReturn.next()) {
    		compoundMap.put(databaseReturn.getInt(1), databaseReturn.getString(2));
    	}
    	return compoundMap;
    }
    
    /**
     * Executes given query.
     * 
     * @param query Query to be executed.
     * @return Returns query results from SQL database.
     */
    private ResultSet executeQuery(final String query) {
    	ResultSet databaseReturn = null;
    	try {
    		databaseReturn = this.statement.executeQuery(query);
    		logger.info("Succesfully queried database with following query: {}", query);
    	} catch (SQLException e) {
    		logger.error("Failed to query database with following query: {}. Error: {}", query, e);
    		this.controls.displayErrorMessage("SQL Exception. Failed to execute the following query: " + query +
    				                          "\n\nError Message: " + e.toString());
    	}
    	return databaseReturn;
    }
    
    /**
     * Load database properties from 'config.properties' file.
     */
    private void loadProperties() {
    	final Properties prop = new Properties();
    	InputStream input = null;
    	
    	try {    
    		input = new FileInputStream("database.properties");

    		//load properties file
    		prop.load(input);
    		this.host = prop.getProperty("host");
    		this.port = prop.getProperty("port");
    		this.username = prop.getProperty("username");
    		this.password = prop.getProperty("password");
    		this.schema = prop.getProperty("schema");
    		this.numCompounds = Integer.parseInt(prop.getProperty("number_of_compounds"));
    		this.numVerticalPartitions = Integer.parseInt(prop.getProperty("vertical_partitions"));
    		this.candoTableName = prop.getProperty("cando_table_name");
    		this.candoFile = prop.getProperty("cando_tab_file");
    		this.compoundTableName = prop.getProperty("compound_table_name");
    		this.compoundMappingFile = prop.getProperty("compound_mapping_tab_file");
    		
    	} catch (IOException ex) {
    		logger.error("Error processing 'config.properties' file for database connection: {}", ex);
    		this.controls.displayErrorMessage("Error processing properties file for database connection. "
    				                        + "Check 'config.properties' file is configured correctly "
    				                        + "and in the same folder as executable jar file.\n\n Error Message: " + ex.toString());
    	} finally {
    		if (input != null) {
    			try {
    				input.close();
    			} catch (IOException e) {
    				logger.error("Error closing config.properties file: {}", e);
    	    		this.controls.displayErrorMessage("IO error closing 'config.properties' file.\n\n Error Message: " + e.toString());
    			}
    		}
    	}
    }
    
    /**
     * Helper method to create string with all the necessary info to connect
     * to a desired database.
     * 
     * @return Returns string used to connect to a database.
     */
    private String createDatabaseConnectionString() {
    	final StringBuilder sb = new StringBuilder();
    	sb.append("jdbc:mysql://");
    	sb.append(this.host);
    	sb.append(":");
    	sb.append(this.port);
    	sb.append("/");
    	sb.append(this.schema);
    	sb.append("?user=");
    	sb.append(this.username);
    	sb.append("&password=");
    	sb.append(this.password);
    	logger.info("Connecting to database with <host:{}, port:{}>...", host, port);

    	return sb.toString();
    }
    
    /**
     * Helper to sort a collection and return the results as a new list.
     * 
     * @param collectionForSorting Collection to be sorted.
     * @return Returns a sorted list.
     */
    private <T extends Comparable<? super T>> List<T> asSortedList(Collection<T> collectionForSorting) {
        final List<T> list = new ArrayList<T>(collectionForSorting);
        java.util.Collections.sort(list);
        return list;
      }
}
